# -*- coding: UTF-8 -*-
# Author: Damon(wuud1)
# CreateDate: 
# Message:  清洗程序

import requests
from lxml import etree
from copy import deepcopy
from setting import *
import time
from untils.requestsTools import requestsTools
from untils.MysqlTools import MysqlTools
import os, re, json, hashlib
from PIL import Image
from sa_desc import sa_desc

class CitroenAll:
    def __init__(self, brand="citroen"):
        self.brand=brand
        self.cookies = {}
        self.basePath = os.getcwd() + os.sep + '..' + os.sep + 'citroenHtml' + os.sep + brand
        if not os.path.exists(self.basePath):
            os.makedirs(self.basePath)
        self.imgBasePath = os.getcwd() + os.sep + '..' + os.sep + 'PNG' + os.sep + brand
        if not os.path.exists(self.imgBasePath):
            os.makedirs(self.imgBasePath)
        self.resTools = requestsTools(basePath=self.basePath)

        self.sqlDb = MysqlTools(db_config=db_config, type='dict', auto_commit=5000)
        self._suffix = "AC" if brand=="citroen" else "AP"
        self.missImg = 0

    def updateCookies(self, setCookies):
        for k in setCookies:
            self.cookies[k] = setCookies[k]

    def get_carList(self):
        if self.brand=="citroen":
            url = 'http://service.citroen.com/docprAC/'
            postData = {
                "tabControlID": "5",
                "jbnContext": "true",
            }
        else:
            url = 'http://service.citroen.com/docprAP/'
            postData = {
                "tabControlID": "15",
                "jbnContext": "true",
            }
        self.resTools.headers = {
            "Host": "service.citroen.com",
            "Cache-Control": "max-age=0",
            "Origin": "http://service.citroen.com",
            "Upgrade-Insecure-Requests": "1",
            "Content-Type": "application/x-www-form-urlencoded",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.96 Safari/537.36",
            "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8",
            "Referer": "http://service.citroen.com/docapvDS/login.do?jbnRedirect=true",
            "Accept-Encoding": "gzip, deflate",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Connection": "keep-alive",
        }
        self.resTools.updateCookies({"_pk_ses.66.196e": "*", "_pk_id.66.196e": "8d0d215f40659ee1.1551407579.1.1551407579.1551407579."})

        response = self.resTools.parseUrl(url=url, method="POST", postData=postData, fileName=self.brand+'CarsList')
        carsListHtml = etree.HTML(response.get("content"))
        carsNameList = carsListHtml.xpath(
            '//table[@class="vehiculeSelection"]/tr[2]//a[contains(@onclick, "onVeh")]/@title')
        carsCodeInfo = carsListHtml.xpath(
            '//table[@class="vehiculeSelection"]/tr[2]//a[contains(@onclick, "onVeh")]/@onclick')
        carList = []
        for index in range(len(carsNameList)):
            _dict = {}
            _dict["name"] = carsNameList[index]
            codeInfo = carsCodeInfo[index]
            cid = codeInfo.split(';')[0].replace("onVehComClick(", "").replace(")", "").replace("'", "").replace(",",
                                                                                                                 "_")
            _dict["cid"] = cid
            print(_dict)
            carList.append(_dict)
        return carList

    def ajaxRequestGetVehCom(self, vehCom, cat, lcdv=False):
        url = 'http://service.citroen.com/docpr{}/ajaxRequestGetVehCom.do'.format(self._suffix)
        self.resTools.headers = {
            "Host": "service.citroen.com",
            "Accept": "text/javascript, text/html, application/xml, text/xml, */*",
            "X-Prototype-Version": "1.6.0.1",
            "Origin": "http://service.citroen.com",
            "X-Requested-With": "XMLHttpRequest",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.96 Safari/537.36",
            "Referer": "http://service.citroen.com/docpr{}/".format(self._suffix),
            "Accept-Encoding": "gzip, deflate",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Connection": "keep-alive",
            "Content-Type": "application/x-www-form-urlencoded; charset=UTF-8",
        }
        postData = {
            "vehCom": vehCom,
            "cat": cat,
            "lcdv": "B0D=&B0F=" if lcdv else "",
            "_": ""
        }
        htmlFile = 'ajaxRequestGetVehCom'+self.cid
        response = self.resTools.parseUrl(url=url, method="POST", postData=postData, fileName=htmlFile+'2' if lcdv else htmlFile+'1')

    def get_carIndex(self, vehCom, cat, isInit=False):
        # 选定车型后的首页
        url = 'http://service.citroen.com/docpr{}/vehCom.do?cat={}&vehCom={}&lcdv=B0D%3D%26B0F%3D'.format(self._suffix, cat, vehCom)
        referer = "http://service.citroen.com/docpr{}/initNav.do".format(self._suffix) if isInit else "http://service.citroen.com/docpr{}/".format(self._suffix)
        self.resTools.headers = {
            "Host": "service.citroen.com",
            "Upgrade-Insecure-Requests": "1",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.96 Safari/537.36",
            "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8",
            "Referer": referer,
            "Accept-Encoding": "gzip, deflate",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Connection": "keep-alive",
        }
        responseInfo = self.resTools.parseUrl(url=url, fileName=self.cid)
        groupInfoList = self.parse_groupInfo(responseInfo.get("content"))
        menuList = etree.HTML(responseInfo.get("content")).xpath('//ul[@class="aside"]/li[2]/ul/li[position()<5]/@id')
        menuList = list(map(lambda x: x.replace("menu_", ""), menuList))

        # 附属的两个请求
        url1 = 'http://service.citroen.com/docpr{}/affichePanier.do'.format(self._suffix)
        self.resTools.headers = {
            "Host": "service.citroen.com",
            "Upgrade-Insecure-Requests": "1",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.96 Safari/537.36",
            "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8",
            "Referer": url,
            "Accept-Encoding": "gzip, deflate",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Connection": "keep-alive",
        }
        #_ = self.resTools.parseUrl(url=url1, fileName='affichePanier'+self.cid)

        url2 = 'http://service.citroen.com/docpr{}/panierTemp.do?typeActionPanierTemp=3'.format(self._suffix)
        self.resTools.headers = {
            "Host": "service.citroen.com",
            "Accept": "text/javascript, text/html, application/xml, text/xml, */*",
            "X-Prototype-Version": "1.6.0.1",
            "X-Requested-With": "XMLHttpRequest",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.96 Safari/537.36",
            "Referer": url,
            "Accept-Encoding": "gzip, deflate",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Connection": "keep-alive",
        }
        #_ = self.resTools.parseUrl(url=url2, fileName='panierTemp'+self.cid)

        return url, menuList, groupInfoList

    def new_search(self, referer):
        print("新的查询")
        url = 'http://service.citroen.com/docpr{}/initNav.do'.format(self._suffix)
        headers = {
            "Host": "service.citroen.com",
            "Upgrade-Insecure-Requests": "1",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.96 Safari/537.36",
            "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8",
            "Referer": referer,
            "Accept-Encoding": "gzip, deflate",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Connection": "keep-alive",
        }
        response = requests.get(url=url, headers=headers, cookies=self.cookies, timeout=30)

    def get_groupInfoList(self, cid, menu, referer):
        self.resTools.headers = {
            "Host": "service.citroen.com",
            "Upgrade-Insecure-Requests": "1",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.96 Safari/537.36",
            "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8",
            "Referer": referer,
            "Accept-Encoding": "gzip, deflate",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Connection": "keep-alive",
        }
        url = 'http://service.citroen.com/docpr{}/fonction.do'.format(self._suffix)
        params = "?{}={}&{}={}&{}={}".format("idFction", menu, "topGrisageActif", "true", "tabTriEtAffich", "1")
        url = url + params
        responseInfo = self.resTools.parseUrl(url=url, fileName=cid+menu)
        groupInfoList = self.parse_groupInfo(responseInfo.get("content"))

        # 附属的两个请求
        url1 = 'http://service.citroen.com/docpr{}/affichePanier.do'.format(self._suffix)
        self.resTools.headers = {
            "Host": "service.citroen.com",
            "Upgrade-Insecure-Requests": "1",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.96 Safari/537.36",
            "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8",
            "Referer": url,
            "Accept-Encoding": "gzip, deflate",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Connection": "keep-alive",
        }
        #_ = self.resTools.parseUrl(url=url1, fileName='affichePanier' + self.cid+ menu)

        url2 = 'http://service.citroen.com/docpr{}/panierTemp.do?typeActionPanierTemp=3'.format(self._suffix)
        self.resTools.headers = {
            "Host": "service.citroen.com",
            "Accept": "text/javascript, text/html, application/xml, text/xml, */*",
            "X-Prototype-Version": "1.6.0.1",
            "X-Requested-With": "XMLHttpRequest",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.96 Safari/537.36",
            "Referer": url,
            "Accept-Encoding": "gzip, deflate",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Connection": "keep-alive",
        }
        #_ = self.resTools.parseUrl(url=url2, fileName='panierTemp' + self.cid + menu)

        return url, groupInfoList

    def parse_groupInfo(self, content):
        jsonstr = re.findall('setFctNiv1Select\((.*?)\)', content)[0]
        _json = json.loads(jsonstr)
        fctionsDependantes = _json.get("fctionsDependantes")
        groupList = []
        for info in fctionsDependantes:
            groupDict = {}
            groupDict["groupName"] = info.get("libelleFct")
            groupDict["groupId"] = info.get("id")
            groupDict["groupImg"] = info.get("imageFct")
            subgroupList = info.get("fctionsDependantes")
            subList = []
            for subgroupInfo in subgroupList:
                subDict = {}
                subDict["subName"] = subgroupInfo.get("libelleFct")
                subDict["subId"] = subgroupInfo.get("id")
                subDict["subImg"] = subgroupInfo.get("imageFct")
                subDict["grisee"] = subgroupInfo.get("grisee")
                subList.append(subDict)
            groupDict["subList"] = subList
            groupList.append(groupDict)
        return groupList


    def parseStrucInfo(self, content):
        _json = json.loads(content)
        strucList = []
        listeDesDocuments = _json.get("listDocPourAffichage", {}).get("listeDesDocuments", [])
        for info in listeDesDocuments:
            strucDict= {}
            strucDict["strucName"] = info.get("titre")
            strucDict["strucModel"] = info.get("applicabilite")
            strucDict["applicabiliteNonTraduite"] = info.get("applicabiliteNonTraduite")
            strucDict["strucId"] = info.get("reference")
            strucDict["refaff"] = info.get("referenceAffichable")
            strucList.append(strucDict)
        return strucList

    def get_strucInfo(self, cid, groupId, subId, referer):
        url1 = 'http://service.citroen.com/docpr{}/AjaxTabRecapDoc.do'.format(self._suffix)
        formData = {
            "idFct": subId,
            "idFctParent": groupId,
            "_": "",
        }
        self.resTools.headers = {
            "Host": "service.citroen.com",
            "Accept": "text/javascript, text/html, application/xml, text/xml, */*",
            "X-Prototype-Version": "1.6.0.1",
            "Origin": "http://service.citroen.com",
            "X-Requested-With": "XMLHttpRequest",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.96 Safari/537.36",
            "Referer": referer,
        }
        _ = self.resTools.parseUrl(url=url1, method="POST", postData=formData, fileName='AjaxTabRecapDoc'+cid+groupId+subId)

        url = 'http://service.citroen.com/docpr{}/AjaxListeDoc.do'.format(self._suffix)
        formData = {
            "typDoc": "",
            "_": ""
        }
        responseInfo = self.resTools.parseUrl(url=url, method="POST", postData=formData, fileName='AjaxListeDoc'+cid+groupId+subId)
        strucInfoList = self.parseStrucInfo(responseInfo.get("content"))
        return strucInfoList

    def parse_parts(self, doc):
        parts = doc.xpath('//div[@id="content"]/table')

        # 获取描点信息字典
        areas = doc.xpath('//map[@name="mapZoneSensible"]/area')
        imgPoints = {}
        for area in areas:
            it = area.xpath('./@id')[0].split("_")[0].replace("area", "")
            points = area.xpath('./@coords')[0].split(", ")
            if it not in imgPoints:
                imgPoints[it] = [points]
            else:
                imgPoints[it].append(points)

        psort = 0
        for tab in parts:
            try:
                pid = tab.xpath('.//tr[1]/td[@class="colref"]/div[@class="prsansprremp"]/text()')[0].replace(" ", "")
                label = tab.xpath('.//tr[1]/td[@class="coldesi"]/div[@class="desi"]/text()')[0]
                cote = tab.xpath('.//tr[1]/td[@class="coldesi"]/div[@class="cote"]/text()')
                label = (label + '(' + cote[0] + ')') if len(cote) > 0 else label
            except:
                continue
            # itid = tab.xpath('./@id')[0].split("-")[0][-1:]
            itid = tab.xpath('./@ondblclick')[0].split("'")[1].split('-')[0]
            for ix, tr in enumerate(tab.xpath('.//tr')):
                psort += 1
                qty = tr.xpath('./td[@class="colcoef"]/div[@class="coef"]/text()')
                qty = qty[0] if len(qty) > 0 else ''
                descomp = tr.xpath('./td[@class="coldesi"]/div[@class="descomp"]/text()')
                descomp = descomp[0] if len(descomp) > 0 else ""
                remark = tr.xpath('./td[@class="coldesi"]/div[@class="microappl"]/text()')
                remark = remark[0] if len(remark) > 0 else ""
                model = tr.xpath('./td[@class="coldesi"]/div[@class="obsligne"]/text()')
                model = model[0] if len(model) > 0 else ""
                _dict = {}
                _dict["pid"] = str(pid)
                _dict["label"] = str(label)
                _dict["psort"] = psort
                _dict["itid"] = str(itid)
                _dict["remark"] = str(remark)
                _dict["quanty"] = str(qty)
                _dict["model"] = str(model)
                _dict["condition"] = str(descomp)
                _dict["img"] = imgPoints.get(itid, [])

                yield _dict

    def get_partInfo(self, cid, strucId, refaff, referer):
        url = 'http://service.citroen.com/docpr{}/affiche.do'.format(self._suffix)
        params = "?{}={}&{}={}&{}={}".format("ref", strucId, "refaff", refaff, "idFct", cid)
        url = url+params
        self.resTools.headers = {
            "Host": "service.citroen.com",
            "Upgrade-Insecure-Requests": "1",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.96 Safari/537.36",
            "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8",
            "Referer": referer,
        }
        responseInfo = self.resTools.parseUrl(url=url, fileName="affiche"+cid+strucId)
        doc = etree.HTML(responseInfo.get("content"))
        parts = self.parse_parts(doc)
        return parts

    def _getData(self, cid, groupInfoList, referer):
        for groupInfo in groupInfoList:
            groupName = groupInfo.get("groupName")
            print('主组', groupName)
            groupId = groupInfo.get("groupId")
            subList = groupInfo.get("subList")

            # TODO 保存到group表
            self.save_group(cid, groupId, groupName)

            for subInfo in subList:
                subName = subInfo.get("subName")
                subId = subInfo.get("subId")
                grisee = subInfo.get("grisee")
                print('子组', subName, grisee)

                # TODO 保存到struc表  subgroup数据
                self.save_sub(cid, groupId, subId, subName)

                if grisee:
                    print(subName, "灰色无数据")
                    continue
                strucInfoList = self.get_strucInfo(cid, groupId, subId, referer)
                for strucInfo in strucInfoList:
                    strucId = strucInfo.get("strucId")
                    strucName = strucInfo.get("strucName")
                    strucModel = strucInfo.get("strucModel")
                    applicabiliteNonTraduite = strucInfo.get("applicabiliteNonTraduite")
                    print('分组', strucName, strucModel)

                    # TODO 保存到struc表
                    self.save_struc(cid, groupId, subId, strucId, strucName, strucModel, applicabiliteNonTraduite)

                    refaff = strucInfo.get("refaff")
                    parts = self.get_partInfo(cid, strucId, refaff, referer)
                    for p in parts:
                        self.save_parts(cid, groupId, subId, strucId, p)

    def _get_md5(self, str):
        '''获取md5加密电子指纹'''
        md5 = hashlib.md5()
        md5.update(str)
        return md5.hexdigest()

    def save2sql(self, _dict, ctype):
        table = tableDict[ctype]
        sql_tail = ' on duplicate key update status=values(`status`)'
        inSql = self.sqlDb.get_insert_sql(table, _dict) + sql_tail
        try:
            self.sqlDb.execute_sql(inSql)
        except Exception as e:
            print(e.__repr__())
            print(inSql)


    def save_info(self, car):
        name = str(car.get("name"))
        cid = str(car.get("cid"))
        _str = name + cid
        _dict = {
            "uid": self._get_md5(_str.encode()),
            "name": name,
            "brand": self.brand,
            "brandCode": self.brand,
            "condition": "",
            "limit": "",
            "limit2": "",
            "limit3": "",
            "limit4": "",
            "label": "",
            "code": cid,
            "description": "",
            "imagePath": "",
            "imgName": "",
            "extra_info": "",
            "updatetime": "now()",
            "createtime": "now()",
            "status": 2,
        }
        self.save2sql(_dict, 'carsinfo')

    def save_group(self, cid, groupId, groupName):
        _str = cid + groupId + groupName
        _dict = {
            "uid": self._get_md5(_str.encode()),
            "cid": cid,
            "num": str(groupId),
            "brandCode": self.brand,
            "label": groupName,
            "extra_info": "",
            "updatetime": "now()",
            "createtime": "now()",
            "status": 1,
            # "": "",
        }
        self.save2sql(_dict, 'group')

    def save_sub(self, cid, groupId, subId, subName):
        _str = cid + groupId + subId + subName
        _dict = {
            "uid": self._get_md5(_str.encode()),
            "cid": cid,
            "num": str(groupId),
            "maingroup": str(groupId),
            "subgroup": subId,
            "mid": "",
            "brandCode": self.brand,
            "name": subName,
            "description": "",
            "model": "",
            "condition": "",
            "imgName": "",
            "imageLarge": "",
            "imgPoints": "",
            "logo": "",
            "extra_info": json.dumps({"filter_rules": []}, ensure_ascii=False),
            "updatetime": "now()",
            "createtime": "now()",
            "status": 1,
        }
        self.save2sql(_dict, 'sub')


    def save_struc(self, cid, groupId, subId, strucId, strucName, strucModel, applicabiliteNonTraduite):
        _str = cid + str(groupId) + subId + strucId + strucName + strucModel
        _dict = {
            "uid": self._get_md5(_str.encode()),
            "cid": cid,
            "num": str(groupId),
            "maingroup": str(groupId),
            "subgroup": subId,
            "mid": strucId,
            "brandCode": self.brand,
            "name": strucName,
            "description": strucModel,
            "model": applicabiliteNonTraduite,
            "condition": "",
            "imgName": "",
            "imageLarge": "",
            "imgPoints": "",
            "logo": "",
            "extra_info": "",
            "updatetime": "now()",
            "createtime": "now()",
            "status": 1,
        }
        self.save2sql(_dict, 'struc')


    def save_parts(self, cid, groupId, subId, strucId, p):
        pid = p.get("pid")
        label = p.get("label")
        psort = p.get("psort")
        itid = p.get("itid")
        remark = p.get("remark")
        quanty = p.get("quanty")
        model = p.get("model")
        condition = p.get("condition")
        img = p.get("img")
        _str = cid + str(groupId) + subId + strucId + pid + str(psort) + itid
        _dict = {
            "cid": cid,
            "uid": self._get_md5(_str.encode()),
            "num": groupId,
            "maingroup": groupId,
            "subgroup": subId,
            "mid": strucId,
            "brandCode": self.brand,
            "pnum": itid,
            "itid": itid,
            "psort": psort,
            "pid": pid,
            "label": label,
            "remark": remark,
            "quantity": quanty,
            "model": model,
            "isreplace": "",
            "replacelabel": "",
            "rid": "",
            "condition": condition,
            "img": json.dumps(img),
            "logo": "",
            "price": "",
            "extra_info": "",
            "updatetime": "now()",
            "createtime": "now()",
            "status": 1,
        }
        self.save2sql(_dict, 'parts')




    def run(self):
        carList = self.get_carList()
        num = 0
        for car in carList:
            cid = car.get("cid")
            name = car.get("name")
            print("车型", car.get("name"), cid)

            # TODO 保存到carsinfo表
            self.save_info(car)

            self.resTools.basePath = self.basePath + os.sep + cid
            if not os.path.exists(self.resTools.basePath):
                os.makedirs(self.resTools.basePath)
            self.cid = cid
            vehCom, cat = cid.split("_")[0], cid.split("_")[1]
            for i in range(4):
                if i == 0:
                    # self.ajaxRequestGetVehCom(vehCom, cat)
                    # self.ajaxRequestGetVehCom(vehCom, cat, lcdv=True)
                    referer, menuList, groupInfoList = self.get_carIndex(vehCom, cat)
                    self._getData(cid, groupInfoList, referer)
                    continue
                menu = menuList[i]
                referer, groupInfoList = self.get_groupInfoList(cid, menu, referer)
                self._getData(cid, groupInfoList, referer)
            self.sqlDb.commit_sql()
            #self.new_search(referer)
        print("end...")

    def get_wh(self, cid, mid):
        img_pth = self.imgBasePath+os.sep+cid+os.sep+mid+'.png'
        pic = Image.open(img_pth)
        return pic.size

    def get_imgPoint_info(self, parts_table, cid, num, subgroup, mid):
        # 获取图片宽高
        try:
            width, height = self.get_wh(cid, mid)
        except:
            width, height = 450, 545
            self.missImg += 1
        sql = '''select * from `{}` where `cid`="{}" and `num`="{}" and `subgroup`="{}" and `mid`="{}"'''.format(parts_table, cid, num, subgroup, mid)
        data = self.sqlDb.select_all(sql)
        _dict = {"hotspots": [], "imageHeight": height, "imageWidth": width, "imageFormat": "image/png", "maxScaleFactor": 1.0}
        for info in data:
            hskey = info.get("itid")
            imgpoint_l = json.loads(info.get("img"))
            if not imgpoint_l:
                continue
            for imgpoint in imgpoint_l:
                hsX = int(int(imgpoint[0])-5)
                hsY = int(int(imgpoint[1])-5)
                maxX = int((int(imgpoint[0])+5))
                maxY = int((int(imgpoint[1])+5))
                _d = {"hsX": hsX, "hsY": hsY, "maxX": maxX, "maxY": maxY, "hsKey": hskey}
                _dict["hotspots"].append(_d)
        return _dict

    def parseImgPoint(self):
        struc_table = tableDict["struc"]
        parts_table = tableDict["parts"]
        # 清洗描点
        sql = '''select * from `{}`'''.format(struc_table)
        data = self.sqlDb.select_all(sql)
        for info in data:
            id = info.get("id")
            cid = info.get("cid")
            num = info.get("num")
            subgroup = info.get("subgroup")
            mid = info.get("mid")
            if not mid:
                continue
            print(cid, num, subgroup, mid)
            imgPoint_info = self.get_imgPoint_info(parts_table, cid, num, subgroup, mid)
            up_sql = '''update `{}` set `imgPoints`='{}' where `id`="{}";'''.format(struc_table, json.dumps(imgPoint_info, ensure_ascii=False), id)
            self.sqlDb.execute_sql(up_sql)
        self.sqlDb.commit_sql()
        print(self.missImg)

    def format_ex(self, ex_raw, return_rules=False):
        if ex_raw.strip("- ") == '':
            return []
        ex_raw = ex_raw.replace("或者", "#or#").replace(" 和 ", "#and#").replace("除了", "#not#").strip('-').strip()
        ex_l1 = ex_raw.split("#or#")
        _l = []
        all_rules = []
        for i in ex_l1:
            # print(i.strip('-').strip(''))
            #####
            _dict = {
                "OPR": "-",
                "date": "-",
                "motor": "-",
                "change_date": "-",
                "exc_sa": [],  # 车架号存在此列表中任意一个条件,直接不通过
                "app_sa": [],
                'use_life': '0'  # 0代表无限制
            }
            i = i.strip('-').strip('')
            # 获取OPR
            res = re.findall(r'起点 OPR .+?\b', i)
            OPR_s = res[0].replace(" ", "").replace("起点OPR", "") if res else ''
            res = re.findall(r'终点 OPR .+?\b', i)
            OPR_e = res[0].replace(" ", "").replace("终点OPR", "") if res else ''
            _dict["OPR"] = OPR_s + '-' + OPR_e

            # 获取change_date
            res = re.findall(r'起点 改进实施日期 .+?\b', i)
            change_s = res[0].replace(" ", "").replace("起点改进实施日期", "") if res else ''
            res = re.findall(r'终点 改进实施日期 .+?\b', i)
            change_e = res[0].replace(" ", "").replace("终点改进实施日期", "") if res else ''
            change_date = change_s + '-' + change_e
            _dict['change_date'] = change_date

            # 获取motor
            res = re.findall(r'起点 发动机 .+?\b', i)
            motor_s = res[0].replace(" ", "").replace("起点发动机", "") if res else ''
            res = re.findall(r'终点 发动机 .+?\b', i)
            motor_e = res[0].replace(" ", "").replace("终点发动机", "") if res else ''
            motor = motor_s + '-' + motor_e
            _dict['motor'] = motor

            # 获取date
            res = re.findall(r'起点 日期 \d+?\b', i)
            date_s = res[0].replace(" ", "").replace("起点日期", "") if res else ''
            res = re.findall(r'终点 日期 \d+?\b', i)
            date_e = res[0].replace(" ", "").replace("终点日期", "") if res else ''
            date = date_s + '-' + date_e
            _dict['date'] = date

            # 获取use_life
            res = re.findall(r'使用年限 >= \d', i)
            use_life = res[0].replace(" ", "").replace("使用年限>=", "") if res else '0'
            _dict['use_life'] = use_life

            # 上述信息获取完,替换掉上述内容
            i = re.sub(r'终点.*?\d\b|起点.*?\d\b', '', i).strip('-').strip()
            i = re.sub(r'起点 改进实施日期 \w+\b|终点 改进实施日期 \w+\b', '', i).strip('-').strip()
            i = re.sub(r'使用年限 >= \d\b', '', i).strip('-').strip()

            # 根据第一个#not#分割, 分割后第一个就是app_sa内容, 第二个就是exc内容
            if '#not#' in i:
                and_str = i.split('#not#', 1)[0]
                not_str = i.split('#not#', 1)[1]
                and_l = and_str.split("#and#")
                not_l = not_str.split("#not#")
                for a in and_l:
                    a = a.strip("-").strip().strip("-").strip().strip("-").strip().replace("#and#", "").replace("#or#",
                                                                                                                "").replace(
                        "#not#", "").replace('"', '').replace("'", "")
                    if a == '':
                        continue
                    _dict['app_sa'].append(a)
                    all_rules.append(a)
                for n in not_l:
                    n = n.strip("-").strip().strip("-").strip().strip("-").strip().replace("#and#", "").replace("#or#",
                                                                                                                "").replace(
                        "#not#", "").replace('"', '').replace("'", "")
                    if n == '':
                        continue
                    _dict['exc_sa'].append(n)
                    all_rules.append(n)

            else:
                and_l = i.split("#and#")
                for a in and_l:
                    a = a.strip("-").strip().strip("-").strip().strip("-").strip().replace("#and#", "").replace("#or#",
                                                                                                                "").replace(
                        "#not#", "").replace('"', '').replace("'", "")
                    if a == '':
                        continue
                    _dict['app_sa'].append(a)
                    all_rules.append(a)
            _l.append(_dict)
            # print(_dict)
        if not return_rules:
            return _l
        else:
            return list(set(all_rules))

    def getRules(self, table, field):
        sql = '''select * from `{}`'''.format(table)
        data = self.sqlDb.select_all(sql)
        _set = set()
        for info in data:
            message = info.get(field)
            if message.strip()=='':
                continue
            rules = self.format_ex(message, return_rules=True)
            for r in rules:
                _set.add(r)
        return _set


    def parseAllDesc(self, descTable):
        # 将分组和零件表所有中文过滤条件做成编号映射关系并入库

        # 获取现有sa_desc 字典
        descDict_now = deepcopy(sa_desc)
        num_max = max([int(i.replace("CP", "")) for i in descDict_now.values()])

        partsTable = tableDict["parts"]
        strucTable = tableDict["struc"]
        # 获取零件表所有过滤条件
        partsRules = self.getRules(partsTable, "remark")
        # 获取分组表所有过滤条件
        strucRules = self.getRules(strucTable, "description")
        allRules = partsRules.union(strucRules)
        num = num_max + 1
        for r in allRules:
            sa = "CP%06d" % num
            desc = r.replace(" ", "").upper()
            if desc in descDict_now:
                continue
            num += 1
            sa_desc[desc] = sa
            print(desc, sa)
        with open('./sa_desc.py' , 'w') as p:
            p.write('sa_desc = '+json.dumps(descDict_now, ensure_ascii=False, indent=4))

    def get_descDict(self, descTable):
        sql = '''select * from `{}`'''.format(descTable)
        data =self.sqlDb.select_all(sql)
        _dict = {}
        for info in data:
            sa = info.get("sa")
            desc = info.get("desc").replace(" ", "").upper()
            _dict[desc] = sa
        return _dict

    def parseExtraInfo(self, descTable, table="all"):
        # 清洗出分组及零件表的extra_info 字段
        def get_sa(_dict, list):
            _l = []
            for i in list:
                sa = _dict.get(i.replace(" ", "").upper(), "")
                if sa == '':
                    continue
                _l.append(sa)
            return _l

        def _clean(table, field):
            sql = '''select * from `{}`'''.format(table)
            data = self.sqlDb.select_all(sql)
            for info in data:
                id = info.get("id")
                msg = info.get(field)
                if msg.strip():
                    _dict = {}
                    extra_info_raw = self.format_ex(msg)
                    for i in extra_info_raw:
                        exc = i.get("exc_sa")
                        app = i.get("app_sa")
                        exc_sa = get_sa(descDict, exc)
                        app_sa = get_sa(descDict, app)
                        i['exc_sa'] = exc_sa
                        i['app_sa'] = app_sa
                    _dict['filter_rules'] = extra_info_raw
                else:
                    _dict = {"filter_rules": []}
                up_sql = '''update `{}` set `extra_info`='{}' where `id`="{}";'''.format(table,
                                                                                         json.dumps(_dict), id)
                self.sqlDb.execute_sql(up_sql)
            self.sqlDb.commit_sql()

        # 获取sa_desc表映射关系字典
        descDict = deepcopy(sa_desc)
        if table.lower()=="all" or table.lower()=='parts':
            parts_table = tableDict["parts"]
            field = 'remark'
            _clean(parts_table, field)
        if table.lower()=="all" or table.lower()=='struc':
            struc_table = tableDict["struc"]
            field = 'description'
            _clean(struc_table, field)


    def get_descDict2File(self, pth, descTable):
        descDict = self.get_descDict(descTable)
        with open(pth , 'w') as p:
            p.write(json.dumps(descDict, ensure_ascii=False, indent=4))

def main():
    brand = "peugeot"  # citroen
    tools = CitroenAll(brand=brand)
    tools.run()  # 清洗主体基本数据
    # tools.parseImgPoint()  # 清洗描点数据
    # tools.parseAllDesc(descTable="sa_desc_fina")  # 所有过滤条件上编号(必须在现有数据上增量)
    # tools.parseExtraInfo(descTable='sa_desc_fina', table='parts')  # 清洗出extra_info 字段

    # 获取sa_desc字典文件
    tools.get_descDict2File('./sa_desc.py', descTable='sa_desc_fina')

if __name__ == '__main__':
    main()